{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to DataFrames\n",
    "**[Bogumił Kamiński](http://bogumilkaminski.pl/about/), November 20, 2020**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "using DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preparing DataFrames for a join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th></tr><tr><th></th><th>Int64?</th><th>String</th></tr></thead><tbody><p>5 rows × 2 columns</p><tr><th>1</th><td>1</td><td>Alice</td></tr><tr><th>2</th><td>2</td><td>Bob</td></tr><tr><th>3</th><td>3</td><td>Conor</td></tr><tr><th>4</th><td>4</td><td>Dave</td></tr><tr><th>5</th><td><em>missing</em></td><td>Zed</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice \\\\\n",
       "\t2 & 2 & Bob \\\\\n",
       "\t3 & 3 & Conor \\\\\n",
       "\t4 & 4 & Dave \\\\\n",
       "\t5 & \\emph{missing} & Zed \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼─────────────────\n",
       "   1 │       1  Alice\n",
       "   2 │       2  Bob\n",
       "   3 │       3  Conor\n",
       "   4 │       4  Dave\n",
       "   5 │\u001b[90m missing \u001b[0m Zed"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(ID=[1,2,3,4,missing], name = [\"Alice\", \"Bob\", \"Conor\", \"Dave\",\"Zed\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>Int64</th></tr></thead><tbody><p>5 rows × 2 columns</p><tr><th>1</th><td>1</td><td>21</td></tr><tr><th>2</th><td>2</td><td>22</td></tr><tr><th>3</th><td>5</td><td>23</td></tr><tr><th>4</th><td>6</td><td>24</td></tr><tr><th>5</th><td><em>missing</em></td><td>99</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& id & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 21 \\\\\n",
       "\t2 & 2 & 22 \\\\\n",
       "\t3 & 5 & 23 \\\\\n",
       "\t4 & 6 & 24 \\\\\n",
       "\t5 & \\emph{missing} & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id      \u001b[0m\u001b[1m age   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │       1     21\n",
       "   2 │       2     22\n",
       "   3 │       5     23\n",
       "   4 │       6     24\n",
       "   5 │\u001b[90m missing \u001b[0m    99"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id=[1,2,5,6,missing], age = [21,22,23,24,99])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rules for the `on` keyword argument:\n",
    "* a single `Symbol` or string if joining on one column with the same name, e.g. `on=:id`\n",
    "* a `Pair` of `Symbol`s or string if joining on one column with different names, e.g. `on=:id=>:id2`\n",
    "* a vector of `Symbol`s or strings if joining on multiple columns with the same name, e.g. `on=[:id1, :id2]`\n",
    "* a vector of `Pair`s of `Symbol`s or strings if joining on multiple columns with the same name, e.g. `on=[:a1=>:a2, :b1=>:b2]`\n",
    "* a vector containing a combination of `Symbol`s or strings or `Pair` of `Symbol`s or strings, e.g. `on=[:a1=>:a2, :b1]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standard joins: inner, left, right, outer, semi, anti"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "ename": "LoadError",
     "evalue": "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
     "output_type": "error",
     "traceback": [
      "ArgumentError: missing values in key columns are not allowed when matchmissing == :error",
      "",
      "Stacktrace:",
      " [1] DataFrames.DataFrameJoiner(::DataFrame, ::DataFrame, ::Pair{Symbol,Symbol}, ::Symbol) at D:\\AppData\\.julia\\packages\\DataFrames\\X0xNW\\src\\abstractdataframe\\join.jl:51",
      " [2] _join(::DataFrame, ::DataFrame; on::Pair{Symbol,Symbol}, kind::Symbol, makeunique::Bool, indicator::Nothing, validate::Tuple{Bool,Bool}, left_rename::typeof(identity), right_rename::typeof(identity), matchmissing::Symbol) at D:\\AppData\\.julia\\packages\\DataFrames\\X0xNW\\src\\abstractdataframe\\join.jl:305",
      " [3] #innerjoin#426 at D:\\AppData\\.julia\\packages\\DataFrames\\X0xNW\\src\\abstractdataframe\\join.jl:600 [inlined]",
      " [4] top-level scope at In[4]:1",
      " [5] include_string(::Function, ::Module, ::String, ::String) at .\\loading.jl:1091"
     ]
    }
   ],
   "source": [
    "innerjoin(x, y, on=:ID=>:id) # missing is not allowed to join-on by default"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>String</th><th>Int64</th></tr></thead><tbody><p>3 rows × 3 columns</p><tr><th>1</th><td>1</td><td>Alice</td><td>21</td></tr><tr><th>2</th><td>2</td><td>Bob</td><td>22</td></tr><tr><th>3</th><td><em>missing</em></td><td>Zed</td><td>99</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼────────────────────────\n",
       "   1 │       1  Alice      21\n",
       "   2 │       2  Bob        22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed        99"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>String</th><th>Int64?</th></tr></thead><tbody><p>5 rows × 3 columns</p><tr><th>1</th><td>1</td><td>Alice</td><td>21</td></tr><tr><th>2</th><td>2</td><td>Bob</td><td>22</td></tr><tr><th>3</th><td>3</td><td>Conor</td><td><em>missing</em></td></tr><tr><th>4</th><td>4</td><td>Dave</td><td><em>missing</em></td></tr><tr><th>5</th><td><em>missing</em></td><td>Zed</td><td>99</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & 3 & Conor & \\emph{missing} \\\\\n",
       "\t4 & 4 & Dave & \\emph{missing} \\\\\n",
       "\t5 & \\emph{missing} & Zed & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age     \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │       1  Alice        21\n",
       "   2 │       2  Bob          22\n",
       "   3 │       3  Conor  \u001b[90m missing \u001b[0m\n",
       "   4 │       4  Dave   \u001b[90m missing \u001b[0m\n",
       "   5 │\u001b[90m missing \u001b[0m Zed          99"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "leftjoin(x, y, on=\"ID\"=>\"id\", matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>String?</th><th>Int64</th></tr></thead><tbody><p>5 rows × 3 columns</p><tr><th>1</th><td>1</td><td>Alice</td><td>21</td></tr><tr><th>2</th><td>2</td><td>Bob</td><td>22</td></tr><tr><th>3</th><td><em>missing</em></td><td>Zed</td><td>99</td></tr><tr><th>4</th><td>5</td><td><em>missing</em></td><td>23</td></tr><tr><th>5</th><td>6</td><td><em>missing</em></td><td>24</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & \\emph{missing} & Zed & 99 \\\\\n",
       "\t4 & 5 & \\emph{missing} & 23 \\\\\n",
       "\t5 & 6 & \\emph{missing} & 24 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m5×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m age   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1  Alice       21\n",
       "   2 │       2  Bob         22\n",
       "   3 │\u001b[90m missing \u001b[0m Zed         99\n",
       "   4 │       5 \u001b[90m missing \u001b[0m    23\n",
       "   5 │       6 \u001b[90m missing \u001b[0m    24"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rightjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>String?</th><th>Int64?</th></tr></thead><tbody><p>7 rows × 3 columns</p><tr><th>1</th><td>1</td><td>Alice</td><td>21</td></tr><tr><th>2</th><td>2</td><td>Bob</td><td>22</td></tr><tr><th>3</th><td>3</td><td>Conor</td><td><em>missing</em></td></tr><tr><th>4</th><td>4</td><td>Dave</td><td><em>missing</em></td></tr><tr><th>5</th><td><em>missing</em></td><td>Zed</td><td>99</td></tr><tr><th>6</th><td>5</td><td><em>missing</em></td><td>23</td></tr><tr><th>7</th><td>6</td><td><em>missing</em></td><td>24</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& ID & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String? & Int64?\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice & 21 \\\\\n",
       "\t2 & 2 & Bob & 22 \\\\\n",
       "\t3 & 3 & Conor & \\emph{missing} \\\\\n",
       "\t4 & 4 & Dave & \\emph{missing} \\\\\n",
       "\t5 & \\emph{missing} & Zed & 99 \\\\\n",
       "\t6 & 5 & \\emph{missing} & 23 \\\\\n",
       "\t7 & 6 & \\emph{missing} & 24 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m7×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m age     \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64?  \u001b[0m\n",
       "─────┼───────────────────────────\n",
       "   1 │       1  Alice         21\n",
       "   2 │       2  Bob           22\n",
       "   3 │       3  Conor   \u001b[90m missing \u001b[0m\n",
       "   4 │       4  Dave    \u001b[90m missing \u001b[0m\n",
       "   5 │\u001b[90m missing \u001b[0m Zed           99\n",
       "   6 │       5 \u001b[90m missing \u001b[0m      23\n",
       "   7 │       6 \u001b[90m missing \u001b[0m      24"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th></tr><tr><th></th><th>Int64?</th><th>String</th></tr></thead><tbody><p>3 rows × 2 columns</p><tr><th>1</th><td>1</td><td>Alice</td></tr><tr><th>2</th><td>2</td><td>Bob</td></tr><tr><th>3</th><td><em>missing</em></td><td>Zed</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & Alice \\\\\n",
       "\t2 & 2 & Bob \\\\\n",
       "\t3 & \\emph{missing} & Zed \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID      \u001b[0m\u001b[1m name   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼─────────────────\n",
       "   1 │       1  Alice\n",
       "   2 │       2  Bob\n",
       "   3 │\u001b[90m missing \u001b[0m Zed"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "semijoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>ID</th><th>name</th></tr><tr><th></th><th>Int64?</th><th>String</th></tr></thead><tbody><p>2 rows × 2 columns</p><tr><th>1</th><td>3</td><td>Conor</td></tr><tr><th>2</th><td>4</td><td>Dave</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& ID & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 3 & Conor \\\\\n",
       "\t2 & 4 & Dave \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m ID     \u001b[0m\u001b[1m name   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64? \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼────────────────\n",
       "   1 │      3  Conor\n",
       "   2 │      4  Dave"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "antijoin(x, y, on=:ID=>:id, matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cross join"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(here no `on` argument)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>x</th><th>y</th></tr><tr><th></th><th>Int64</th><th>String</th></tr></thead><tbody><p>6 rows × 2 columns</p><tr><th>1</th><td>1</td><td>a</td></tr><tr><th>2</th><td>1</td><td>b</td></tr><tr><th>3</th><td>1</td><td>c</td></tr><tr><th>4</th><td>2</td><td>a</td></tr><tr><th>5</th><td>2</td><td>b</td></tr><tr><th>6</th><td>2</td><td>c</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cc}\n",
       "\t& x & y\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & a \\\\\n",
       "\t2 & 1 & b \\\\\n",
       "\t3 & 1 & c \\\\\n",
       "\t4 & 2 & a \\\\\n",
       "\t5 & 2 & b \\\\\n",
       "\t6 & 2 & c \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m x     \u001b[0m\u001b[1m y      \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼───────────────\n",
       "   1 │     1  a\n",
       "   2 │     1  b\n",
       "   3 │     1  c\n",
       "   4 │     2  a\n",
       "   5 │     2  b\n",
       "   6 │     2  c"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crossjoin(DataFrame(x=[1,2]), DataFrame(y=[\"a\",\"b\",\"c\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Complex cases of joins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>name</th></tr><tr><th></th><th>Int64?</th><th>Int64?</th><th>String</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>Alice</td></tr><tr><th>2</th><td>1</td><td>11</td><td>Bob</td></tr><tr><th>3</th><td>2</td><td>2</td><td>Conor</td></tr><tr><th>4</th><td>2</td><td>21</td><td>Dave</td></tr><tr><th>5</th><td><em>missing</em></td><td><em>missing</em></td><td>Zed</td></tr><tr><th>6</th><td><em>missing</em></td><td>99</td><td>Zoe</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & name\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & Alice \\\\\n",
       "\t2 & 1 & 11 & Bob \\\\\n",
       "\t3 & 2 & 2 & Conor \\\\\n",
       "\t4 & 2 & 21 & Dave \\\\\n",
       "\t5 & \\emph{missing} & \\emph{missing} & Zed \\\\\n",
       "\t6 & \\emph{missing} & 99 & Zoe \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │       1        1  Alice\n",
       "   2 │       1       11  Bob\n",
       "   3 │       2        2  Conor\n",
       "   4 │       2       21  Dave\n",
       "   5 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed\n",
       "   6 │\u001b[90m missing \u001b[0m      99  Zoe"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id1=[1,1,2,2,missing,missing],\n",
    "              id2=[1,11,2,21,missing,99],\n",
    "              name = [\"Alice\", \"Bob\", \"Conor\", \"Dave\",\"Zed\", \"Zoe\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>Int64?</th><th>Int64</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>1</td><td>11</td><td>21</td></tr><tr><th>2</th><td>1</td><td>1</td><td>22</td></tr><tr><th>3</th><td>3</td><td>31</td><td>23</td></tr><tr><th>4</th><td>3</td><td>3</td><td>24</td></tr><tr><th>5</th><td><em>missing</em></td><td><em>missing</em></td><td>99</td></tr><tr><th>6</th><td><em>missing</em></td><td>999</td><td>100</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 11 & 21 \\\\\n",
       "\t2 & 1 & 1 & 22 \\\\\n",
       "\t3 & 3 & 31 & 23 \\\\\n",
       "\t4 & 3 & 3 & 24 \\\\\n",
       "\t5 & \\emph{missing} & \\emph{missing} & 99 \\\\\n",
       "\t6 & \\emph{missing} & 999 & 100 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m age   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────\n",
       "   1 │       1       11     21\n",
       "   2 │       1        1     22\n",
       "   3 │       3       31     23\n",
       "   4 │       3        3     24\n",
       "   5 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m    99\n",
       "   6 │\u001b[90m missing \u001b[0m     999    100"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id1=[1,1,3,3,missing,missing],\n",
    "              id2=[11,1,31,3,missing,999],\n",
    "              age = [21,22,23,24,99, 100])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>name</th><th>age</th></tr><tr><th></th><th>Int64?</th><th>Int64?</th><th>String</th><th>Int64</th></tr></thead><tbody><p>3 rows × 4 columns</p><tr><th>1</th><td>1</td><td>1</td><td>Alice</td><td>22</td></tr><tr><th>2</th><td>1</td><td>11</td><td>Bob</td><td>21</td></tr><tr><th>3</th><td><em>missing</em></td><td><em>missing</em></td><td>Zed</td><td>99</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id1 & id2 & name & age\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & Alice & 22 \\\\\n",
       "\t2 & 1 & 11 & Bob & 21 \\\\\n",
       "\t3 & \\emph{missing} & \\emph{missing} & Zed & 99 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m3×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name   \u001b[0m\u001b[1m age   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼─────────────────────────────────\n",
       "   1 │       1        1  Alice      22\n",
       "   2 │       1       11  Bob        21\n",
       "   3 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed        99"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=[:id1, :id2], matchmissing=:equal) # joining on two columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>name</th><th>id2_1</th><th>age</th><th>source</th></tr><tr><th></th><th>Int64?</th><th>Int64?</th><th>String?</th><th>Int64?</th><th>Int64?</th><th>String</th></tr></thead><tbody><p>12 rows × 6 columns</p><tr><th>1</th><td>1</td><td>1</td><td>Alice</td><td>11</td><td>21</td><td>both</td></tr><tr><th>2</th><td>1</td><td>1</td><td>Alice</td><td>1</td><td>22</td><td>both</td></tr><tr><th>3</th><td>1</td><td>11</td><td>Bob</td><td>11</td><td>21</td><td>both</td></tr><tr><th>4</th><td>1</td><td>11</td><td>Bob</td><td>1</td><td>22</td><td>both</td></tr><tr><th>5</th><td>2</td><td>2</td><td>Conor</td><td><em>missing</em></td><td><em>missing</em></td><td>left_only</td></tr><tr><th>6</th><td>2</td><td>21</td><td>Dave</td><td><em>missing</em></td><td><em>missing</em></td><td>left_only</td></tr><tr><th>7</th><td><em>missing</em></td><td><em>missing</em></td><td>Zed</td><td><em>missing</em></td><td>99</td><td>both</td></tr><tr><th>8</th><td><em>missing</em></td><td><em>missing</em></td><td>Zed</td><td>999</td><td>100</td><td>both</td></tr><tr><th>9</th><td><em>missing</em></td><td>99</td><td>Zoe</td><td><em>missing</em></td><td>99</td><td>both</td></tr><tr><th>10</th><td><em>missing</em></td><td>99</td><td>Zoe</td><td>999</td><td>100</td><td>both</td></tr><tr><th>11</th><td>3</td><td><em>missing</em></td><td><em>missing</em></td><td>31</td><td>23</td><td>right_only</td></tr><tr><th>12</th><td>3</td><td><em>missing</em></td><td><em>missing</em></td><td>3</td><td>24</td><td>right_only</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& id1 & id2 & name & id2\\_1 & age & source\\\\\n",
       "\t\\hline\n",
       "\t& Int64? & Int64? & String? & Int64? & Int64? & String\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & Alice & 11 & 21 & both \\\\\n",
       "\t2 & 1 & 1 & Alice & 1 & 22 & both \\\\\n",
       "\t3 & 1 & 11 & Bob & 11 & 21 & both \\\\\n",
       "\t4 & 1 & 11 & Bob & 1 & 22 & both \\\\\n",
       "\t5 & 2 & 2 & Conor & \\emph{missing} & \\emph{missing} & left\\_only \\\\\n",
       "\t6 & 2 & 21 & Dave & \\emph{missing} & \\emph{missing} & left\\_only \\\\\n",
       "\t7 & \\emph{missing} & \\emph{missing} & Zed & \\emph{missing} & 99 & both \\\\\n",
       "\t8 & \\emph{missing} & \\emph{missing} & Zed & 999 & 100 & both \\\\\n",
       "\t9 & \\emph{missing} & 99 & Zoe & \\emph{missing} & 99 & both \\\\\n",
       "\t10 & \\emph{missing} & 99 & Zoe & 999 & 100 & both \\\\\n",
       "\t11 & 3 & \\emph{missing} & \\emph{missing} & 31 & 23 & right\\_only \\\\\n",
       "\t12 & 3 & \\emph{missing} & \\emph{missing} & 3 & 24 & right\\_only \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m12×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1     \u001b[0m\u001b[1m id2     \u001b[0m\u001b[1m name    \u001b[0m\u001b[1m id2_1   \u001b[0m\u001b[1m age     \u001b[0m\u001b[1m source     \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String? \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m Int64?  \u001b[0m\u001b[90m String     \u001b[0m\n",
       "─────┼─────────────────────────────────────────────────────────\n",
       "   1 │       1        1  Alice         11       21  both\n",
       "   2 │       1        1  Alice          1       22  both\n",
       "   3 │       1       11  Bob           11       21  both\n",
       "   4 │       1       11  Bob            1       22  both\n",
       "   5 │       2        2  Conor   \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m left_only\n",
       "   6 │       2       21  Dave    \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m left_only\n",
       "   7 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed     \u001b[90m missing \u001b[0m      99  both\n",
       "   8 │\u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m Zed          999      100  both\n",
       "   9 │\u001b[90m missing \u001b[0m      99  Zoe     \u001b[90m missing \u001b[0m      99  both\n",
       "  10 │\u001b[90m missing \u001b[0m      99  Zoe          999      100  both\n",
       "  11 │       3 \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m      31       23  right_only\n",
       "  12 │       3 \u001b[90m missing \u001b[0m\u001b[90m missing \u001b[0m       3       24  right_only"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outerjoin(x, y, on=:id1, makeunique=true, indicator=:source, matchmissing=:equal) # with duplicates all combinations are produced"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "LoadError",
     "evalue": "ArgumentError: Merge key(s) are not unique in both df1 and df2. df1 contains 3 duplicate keys: NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((missing,)). df2 contains 3 duplicate keys: NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((missing,)).",
     "output_type": "error",
     "traceback": [
      "ArgumentError: Merge key(s) are not unique in both df1 and df2. df1 contains 3 duplicate keys: NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((missing,)). df2 contains 3 duplicate keys: NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((1,)), ..., NamedTuple{(:id1,),Tuple{Union{Missing, Int64}}}((missing,)).",
      "",
      "Stacktrace:",
      " [1] _join(::DataFrame, ::DataFrame; on::Symbol, kind::Symbol, makeunique::Bool, indicator::Nothing, validate::Tuple{Bool,Bool}, left_rename::typeof(identity), right_rename::typeof(identity), matchmissing::Symbol) at D:\\AppData\\.julia\\packages\\DataFrames\\X0xNW\\src\\abstractdataframe\\join.jl:372",
      " [2] #innerjoin#426 at D:\\AppData\\.julia\\packages\\DataFrames\\X0xNW\\src\\abstractdataframe\\join.jl:600 [inlined]",
      " [3] top-level scope at In[16]:2",
      " [4] include_string(::Function, ::Module, ::String, ::String) at .\\loading.jl:1091"
     ]
    }
   ],
   "source": [
    "# you can force validation of uniqueness of key on which you join\n",
    "innerjoin(x, y, on=:id1, makeunique=true, validate=(true,true), matchmissing=:equal)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "mixed `on` argument for joining on multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>x1</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Char</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>a</td></tr><tr><th>2</th><td>2</td><td>2</td><td>b</td></tr><tr><th>3</th><td>3</td><td>1</td><td>c</td></tr><tr><th>4</th><td>4</td><td>2</td><td>d</td></tr><tr><th>5</th><td>5</td><td>1</td><td>e</td></tr><tr><th>6</th><td>6</td><td>2</td><td>f</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & id2 & x1\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a \\\\\n",
       "\t2 & 2 & 2 & b \\\\\n",
       "\t3 & 3 & 1 & c \\\\\n",
       "\t4 & 4 & 2 & d \\\\\n",
       "\t5 & 5 & 1 & e \\\\\n",
       "\t6 & 6 & 2 & f \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m id2   \u001b[0m\u001b[1m x1   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼────────────────────\n",
       "   1 │     1      1  a\n",
       "   2 │     2      2  b\n",
       "   3 │     3      1  c\n",
       "   4 │     4      2  d\n",
       "   5 │     5      1  e\n",
       "   6 │     6      2  f"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x = DataFrame(id1=1:6, id2=[1,2,1,2,1,2], x1 = 'a':'f')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>ID2</th><th>x2</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Char</th></tr></thead><tbody><p>6 rows × 3 columns</p><tr><th>1</th><td>1</td><td>1</td><td>a</td></tr><tr><th>2</th><td>2</td><td>2</td><td>b</td></tr><tr><th>3</th><td>3</td><td>3</td><td>c</td></tr><tr><th>4</th><td>4</td><td>4</td><td>d</td></tr><tr><th>5</th><td>5</td><td>5</td><td>e</td></tr><tr><th>6</th><td>6</td><td>6</td><td>f</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ccc}\n",
       "\t& id1 & ID2 & x2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a \\\\\n",
       "\t2 & 2 & 2 & b \\\\\n",
       "\t3 & 3 & 3 & c \\\\\n",
       "\t4 & 4 & 4 & d \\\\\n",
       "\t5 & 5 & 5 & e \\\\\n",
       "\t6 & 6 & 6 & f \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×3 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m ID2   \u001b[0m\u001b[1m x2   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼────────────────────\n",
       "   1 │     1      1  a\n",
       "   2 │     2      2  b\n",
       "   3 │     3      3  c\n",
       "   4 │     4      4  d\n",
       "   5 │     5      5  e\n",
       "   6 │     6      6  f"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y = DataFrame(id1=1:6, ID2=1:6, x2 = 'a':'f')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id1</th><th>id2</th><th>x1</th><th>x2</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Char</th><th>Char</th></tr></thead><tbody><p>2 rows × 4 columns</p><tr><th>1</th><td>1</td><td>1</td><td>a</td><td>a</td></tr><tr><th>2</th><td>2</td><td>2</td><td>b</td><td>b</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccc}\n",
       "\t& id1 & id2 & x1 & x2\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Char & Char\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 1 & a & a \\\\\n",
       "\t2 & 2 & 2 & b & b \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m2×4 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id1   \u001b[0m\u001b[1m id2   \u001b[0m\u001b[1m x1   \u001b[0m\u001b[1m x2   \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Char \u001b[0m\u001b[90m Char \u001b[0m\n",
       "─────┼──────────────────────────\n",
       "   1 │     1      1  a     a\n",
       "   2 │     2      2  b     b"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(x, y, on=[:id1, :id2=>:ID2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "joining more than two data frames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5-element Array{DataFrame,1}:\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v1    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     11\n",
       "   2 │     2     12\n",
       "   3 │     3     13\n",
       "   4 │     4     14\n",
       "   5 │     5     15\n",
       "   6 │     6     16\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v2    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     21\n",
       "   2 │     2     22\n",
       "   3 │     3     23\n",
       "   4 │     4     24\n",
       "   5 │     5     25\n",
       "   6 │     6     26\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v3    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     31\n",
       "   2 │     2     32\n",
       "   3 │     3     33\n",
       "   4 │     4     34\n",
       "   5 │     5     35\n",
       "   6 │     6     36\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v4    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     41\n",
       "   2 │     2     42\n",
       "   3 │     3     43\n",
       "   4 │     4     44\n",
       "   5 │     5     45\n",
       "   6 │     6     46\n",
       " \u001b[1m6×2 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v5    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────\n",
       "   1 │     1     51\n",
       "   2 │     2     52\n",
       "   3 │     3     53\n",
       "   4 │     4     54\n",
       "   5 │     5     55\n",
       "   6 │     6     56"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "xs = [DataFrame(\"id\"=>1:6, \"v$i\"=>((1:6) .+ 10i)) for i in 1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"data-frame\"><thead><tr><th></th><th>id</th><th>v1</th><th>v2</th><th>v3</th><th>v4</th><th>v5</th></tr><tr><th></th><th>Int64</th><th>Int64</th><th>Int64</th><th>Int64</th><th>Int64</th><th>Int64</th></tr></thead><tbody><p>6 rows × 6 columns</p><tr><th>1</th><td>1</td><td>11</td><td>21</td><td>31</td><td>41</td><td>51</td></tr><tr><th>2</th><td>2</td><td>12</td><td>22</td><td>32</td><td>42</td><td>52</td></tr><tr><th>3</th><td>3</td><td>13</td><td>23</td><td>33</td><td>43</td><td>53</td></tr><tr><th>4</th><td>4</td><td>14</td><td>24</td><td>34</td><td>44</td><td>54</td></tr><tr><th>5</th><td>5</td><td>15</td><td>25</td><td>35</td><td>45</td><td>55</td></tr><tr><th>6</th><td>6</td><td>16</td><td>26</td><td>36</td><td>46</td><td>56</td></tr></tbody></table>"
      ],
      "text/latex": [
       "\\begin{tabular}{r|cccccc}\n",
       "\t& id & v1 & v2 & v3 & v4 & v5\\\\\n",
       "\t\\hline\n",
       "\t& Int64 & Int64 & Int64 & Int64 & Int64 & Int64\\\\\n",
       "\t\\hline\n",
       "\t1 & 1 & 11 & 21 & 31 & 41 & 51 \\\\\n",
       "\t2 & 2 & 12 & 22 & 32 & 42 & 52 \\\\\n",
       "\t3 & 3 & 13 & 23 & 33 & 43 & 53 \\\\\n",
       "\t4 & 4 & 14 & 24 & 34 & 44 & 54 \\\\\n",
       "\t5 & 5 & 15 & 25 & 35 & 45 & 55 \\\\\n",
       "\t6 & 6 & 16 & 26 & 36 & 46 & 56 \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "\u001b[1m6×6 DataFrame\u001b[0m\n",
       "\u001b[1m Row \u001b[0m│\u001b[1m id    \u001b[0m\u001b[1m v1    \u001b[0m\u001b[1m v2    \u001b[0m\u001b[1m v3    \u001b[0m\u001b[1m v4    \u001b[0m\u001b[1m v5    \u001b[0m\n",
       "\u001b[1m     \u001b[0m│\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\u001b[90m Int64 \u001b[0m\n",
       "─────┼──────────────────────────────────────────\n",
       "   1 │     1     11     21     31     41     51\n",
       "   2 │     2     12     22     32     42     52\n",
       "   3 │     3     13     23     33     43     53\n",
       "   4 │     4     14     24     34     44     54\n",
       "   5 │     5     15     25     35     45     55\n",
       "   6 │     6     16     26     36     46     56"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "innerjoin(xs..., on=:id) # also for outerjoin and crossjoin"
   ]
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "kernelspec": {
   "display_name": "Julia 1.5.3",
   "language": "julia",
   "name": "julia-1.5"
  },
  "language_info": {
   "file_extension": ".jl",
   "mimetype": "application/julia",
   "name": "julia",
   "version": "1.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
